﻿using Newtonsoft.Json.Linq;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;

namespace XBJCYGC.Controllers
{
    public class CourseController : ApiController
    {
        [HttpPost]
        [CYGCExceptionFilter("获取课程列表错误")]
        public DataTable getList(JObject opt)
        {
            /* OracleParameter[] para = new OracleParameter[1]{
                 new OracleParameter("username",opt["name"].ToString())
             };*/
            return DB.CYGC.Query(@"select id, kczt, title, author,  abstract, kssj, yjsc, skdd, tkcs, dzcs from a_course order by kssj desc");
        }
        [HttpPost]
        [CYGCExceptionFilter("管理密码确认时错误")]
        public decimal qrmm(JObject opt)
        {
            OracleParameter[] para = new OracleParameter[2]{
                 new OracleParameter("ID",opt["id"].ToString()),
                 new OracleParameter("pwd",opt["pwd"].ToString())
             };
            return DB.CYGC.QueryOneDecimal(@"select count(1) from a_course where id=:id and pwd=:pwd", para);
        }
        [HttpPost]
        [CYGCExceptionFilter("获取课程内容时错误")]
        public DataTable getOneCourse(JObject opt)
        {
            OracleParameter[] para = new OracleParameter[2]{
                 new OracleParameter("ID",opt["id"].ToString()),
                 new OracleParameter("pwd",opt["pwd"].ToString())
             };
            return DB.CYGC.Query(@"select * from a_course where id=:id and pwd=:pwd", para);
        }
        [HttpPost]
        [CYGCExceptionFilter("保存课程内容错误")]
        public void FormSavewithSEQ(JObject opt)
        {
            //用序列来做ID
            var table = opt["_table"].ToString().Trim();
            //目前仅支持单列的主键。联合主键先不考虑
            var keys = opt["_keys"].ToString().Trim();
            var seq = opt["_seq"].ToString().Trim();
            string[] filterArr = { "_table", "_keys", "_seq" };
            string sql;
            List<string> fields = new List<string>();
            List<string> values = new List<string>();


            //1、 如果不存在主键，表示是新增.则主键补一个GUID
            if (opt[keys].ToString().Trim() == "")
            {
                foreach (var kv in opt)
                {
                    if (filterArr.Contains(kv.Key)) continue;
                    //如果是主键，则要生成一个id
                    if (kv.Key == keys)
                    {
                        fields.Add(kv.Key);
                        values.Add($"{seq}.NEXTVAL");
                    }
                    else if (kv.Key == "Kssj")
                    {
                        fields.Add(kv.Key);
                        values.Add(kv.Value.ToString());
                    }
                    else
                    {
                        fields.Add(kv.Key);
                        values.Add($"'{kv.Value.ToString()}'");
                    }
                }
                sql = $"insert into {table} ({string.Join(",", fields)}) values ({ string.Join(",", values)})";
            }
            else
            {
                foreach (var kv in opt)
                {
                    if (filterArr.Contains(kv.Key) || kv.Key == keys) continue;
                    if(kv.Key == "Kssj")
                    {
                        fields.Add($"{kv.Key}={kv.Value}");
                    }
                    else { 
                    fields.Add($"{kv.Key}='{kv.Value}'");
                    }
                }
                sql = $"update {table} set { string.Join(",", fields)} where {keys}='{opt[keys].ToString()}'";
            }
            DB.CYGC.Execute(sql);
        }
    }
}
